﻿using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.CodeDom;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Tool.Helper
{
    public class ExcelHelperEx : IDisposable
    {
        public IWorkbook Workbook { get; set; }
        public ISheet Sheet { get; set; }

        /// <summary>
        /// 读取EXCEL表时初始化
        /// </summary>
        /// <param name="filePath"></param>
        public bool ReadExcelInit(string filePath)
        {
            var extension = Path.GetExtension(filePath);
            if (extension != null)
            {
                string fileExt = extension.ToLower();
                if (fileExt == ".xlsx")
                {
                    FileStream fileStr = File.OpenRead(filePath);
                    Workbook = new XSSFWorkbook(fileStr); ;
                    SetSheetIndex(0);
                    return true;
                }
                if (fileExt == ".xls")
                {
                    FileStream fileStr = File.OpenRead(filePath);
                    Workbook = new HSSFWorkbook(fileStr);
                    SetSheetIndex(0);
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 设置工作簿中当前工作表，从0开始
        /// </summary>
        /// <param name="index"></param>
        public void SetSheetIndex(int index)
        {
             Sheet = Workbook.GetSheetAt(index);
        }

        /// <summary>
        /// 表格合并
        /// </summary>
        /// <param name="firstRow">第一行</param>
        /// <param name="lastRow">最后一行</param>
        /// <param name="firstCol">第一排</param>
        /// <param name="lastCol">最后一排</param>
        public void SetMergedRegion(int firstRow, int lastRow, int firstCol, int lastCol)
        {
            Sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }

        /// <summary>
        /// 插入图片
        /// </summary>
        /// <param name="picturePath"></param>
        /// <param name="dx1"></param>
        /// <param name="dy1"></param>
        /// <param name="dx2"></param>
        /// <param name="dy2"></param>
        /// <param name="col1"></param>
        /// <param name="row1"></param>
        /// <param name="col2"></param>
        /// <param name="row2"></param>
        public void InsertPicture(string picturePath, int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
        {
            byte[] bytes = File.ReadAllBytes(@picturePath);
            string str = GetExtensionName(picturePath);
            int picId = Workbook.AddPicture(bytes, GetPictureType(str));
            if (Workbook is XSSFWorkbook)
            {
            }
            else if (Workbook is HSSFWorkbook)
            {
                HSSFPatriarch patriarch = (HSSFPatriarch)Sheet.CreateDrawingPatriarch();
                IClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
                IPicture pict = patriarch.CreatePicture(anchor, picId);
                //   pict.Resize();
            }
        }

        /// <summary>
        /// 根据文件名取得扩展名
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private string GetExtensionName(string fileName)
        {
            var extension = Path.GetExtension(fileName);
            if (extension != null)
            {
                return extension.ToLower();
            }
            return null;
        }

        /// <summary>
        /// 根据扩展名获取图片类型
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private PictureType GetPictureType(string str)
        {
            PictureType type;
            switch (str)
            {
                case "emf":
                    type = PictureType.EMF;
                    break;
                case "wmf":
                    type = PictureType.WMF;
                    break;
                case "jpeg":
                case "jpg":
                    type = PictureType.JPEG;
                    break;
                case "png":
                    type = PictureType.PNG;
                    break;
                case "dib":
                    type = PictureType.DIB;
                    break;
                case "gif":
                    type = PictureType.GIF;
                    break;
                case "eps":
                    type = PictureType.EPS;
                    break;
                case "bmp":
                    type = PictureType.BMP;
                    break;
                default:
                    type = PictureType.PNG;
                    break;
            }
            return type;
        }

        #region   //设置和获取单元格值

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="cellIndex"></param>
        /// <returns></returns>
        public object GetRowCellValue(int rowIndex, int cellIndex)
        {
            object objValue = "";
            try
            {
                if (Sheet?.GetRow(rowIndex) == null)
                {
                    return "";
                }
                ICell cl = Sheet.GetRow(rowIndex).GetCell(cellIndex);
                if (cl == null)
                {
                    return "";
                }
                switch (cl.CellType)
                {
                    case CellType.Numeric:
                        //if (DateUtil.IsValidExcelDate(cl.NumericCellValue))
                        //{
                        //    objValue = cl.DateCellValue;
                        //}
                        //else
                        if (DateUtil.IsCellInternalDateFormatted(cl))
                        {
                            
                            objValue = DateUtil.GetJavaDate(cl.NumericCellValue);
                        }
                        else if (DateUtil.IsCellDateFormatted(cl))
                        {
                            objValue = DateUtil.GetJavaDate(cl.NumericCellValue); ;
                        }
                        else
                        {
                            objValue = cl.NumericCellValue;
                        }
                        break;
                    case CellType.String:
                        objValue = cl.StringCellValue;
                        break; ;
                    case CellType.Blank:
                        objValue = "";
                        break; ;
                    default:
                        objValue = cl.StringCellValue;
                        break;
                }

            }
            catch (Exception)
            {

            }
            return objValue;
        }


        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cl"></param>
        /// <returns></returns>
        public object GetRowCellValue(ICell cl)
        {
            object objValue = null;
            try
            {
                if (cl == null)
                {
                    return null;
                }
                switch (cl.CellType)
                {
                    case CellType.Numeric:
                        //if (DateUtil.IsValidExcelDate(cl.NumericCellValue))
                        //{
                        //    objValue = cl.DateCellValue;
                        //}
                        //else
                        if (DateUtil.IsCellInternalDateFormatted(cl))
                        {
                            objValue = DateUtil.GetJavaDate(cl.NumericCellValue);
                        }
                        else if (DateUtil.IsCellDateFormatted(cl))
                        {
                            objValue = DateUtil.GetJavaDate(cl.NumericCellValue); ;
                        }
                        else
                        {
                            objValue = cl.NumericCellValue;
                        }
                        break;
                    case CellType.String:
                        objValue = cl.StringCellValue;
                        break; ;
                    case CellType.Blank:
                        objValue = "";
                        break; ;
                    default:
                        objValue = cl.StringCellValue;
                        break;
                }

            }
            catch (Exception)
            {

            }
            return objValue;
        }

        /// <summary>
        /// 获取指定行单元格
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="cellIndex"></param>
        /// <returns></returns>
        public ICell GetCell(int rowIndex,int cellIndex, bool isStyle=false)
        {
            IRow row;
            ICell cell;
            row = Sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = Sheet.CreateRow(rowIndex);
                cell = row.CreateCell(cellIndex);
                if (isStyle)
                {
                    cell.CellStyle = _borderstyle;
                }
                
            }
            else
            {
                cell = row.GetCell(cellIndex);
                if (cell == null)
                {
                    cell = row.CreateCell(cellIndex);
                    if (isStyle)
                    {
                        cell.CellStyle = _borderstyle;
                    }
                }
            }
            return cell;
        }

        /// <summary>
        /// 设置指定单元格值
        /// </summary>
        /// <param name="rowInex"></param>
        /// <param name="cellIndex"></param>
        /// <param name="data"></param>
        public void SetValue(int rowIndex, int cellIndex, int data,bool isStyle=false)
        {
            var cell= GetCell(rowIndex, cellIndex,isStyle);
            cell.SetCellValue(data);
        }
        /// <summary>
        /// 设置指定单元格值
        /// </summary>
        /// <param name="rowInex"></param>
        /// <param name="cellIndex"></param>
        /// <param name="data"></param>
        public void SetValue(int rowIndex, int cellIndex, string data, bool isStyle = false)
        {
            var cell = GetCell(rowIndex, cellIndex, isStyle);
            cell.SetCellValue(data);
        }
        /// <summary>
        /// 设置指定单元格值
        /// </summary>
        /// <param name="rowInex"></param>
        /// <param name="cellIndex"></param>
        /// <param name="data"></param>
        public void SetValue(int rowIndex, int cellIndex, double data, bool isStyle = false)
        {
            var cell = GetCell(rowIndex, cellIndex, isStyle);
            cell.SetCellValue(data);
        }
        /// <summary>
        /// 设置指定单元格值
        /// </summary>
        /// <param name="rowInex"></param>
        /// <param name="cellIndex"></param>
        /// <param name="data"></param>
        public void SetValue(int rowIndex, int cellIndex, DateTime data, bool isStyle = false)
        {
            var cell = GetCell(rowIndex, cellIndex, isStyle);
            cell.SetCellValue(data);
        }

       
        #endregion

        #region   //获取基本信息

        /// <summary>
        /// 获取指定列不为空的最大行数
        /// </summary>
        /// <param name="cellIndex">列Index</param>
        /// <returns></returns>
        public int GetLastRowNumber(int cellIndex,int minRow=0)
        {

            for (int value = minRow; ; value++)
            {
                if (Sheet.GetRow(value) == null || Sheet.GetRow(value).GetCell(cellIndex) == null)
                {
                    return value;
                }
            }
        }

        /// <summary>
        /// 获取指定行最大单元格数
        /// </summary>
        /// <param name="rowIndex">指定行Index</param>
        /// <returns></returns>
        public int GetLastCellNumber(int rowIndex)
        {
            if (Sheet.GetRow(rowIndex) == null)
            {
                return -1;
            }
            for (int value = 0; ; value++)
            {
                if (Sheet.GetRow(rowIndex).GetCell(value) == null || Sheet.GetRow(rowIndex).GetCell(value).ToString().Trim().Length == 0)
                {
                    return value;
                }
            }
        }

        /// <summary>
        /// 获取工作簿中工作表数量
        /// </summary>
        /// <returns></returns>
        public int GetSheetCount()
        {
            return Workbook.NumberOfSheets;
        }


        #endregion

        #region  //创建表格

        /// <summary>
        /// 创建工作簿
        /// </summary>
        /// <param name="fileName">文件名称</param>
        public void CreateWorkbook(string fileName)
        {
            var extension = Path.GetExtension(fileName);
            if (extension != null)
            {
                string fileExt = extension.ToLower();
                if (fileExt == ".xlsx")
                {
                    Workbook = new XSSFWorkbook(); ;
                }
                else
                {
                    Workbook = new HSSFWorkbook();
                }
            }
            else
            {
                Workbook = new XSSFWorkbook();
            }
            CellStyleInit();
        }

        /// <summary>
        /// 创建工作表
        /// </summary>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public void CreateSheet(string sheetName="")
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "sheet" + (GetSheetCount() + 1).ToString();
            }
             Sheet = Workbook.CreateSheet(sheetName);
        }

        /// <summary>
        /// 创建行
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public IRow CreateRow(int index)
        {
            return Sheet.CreateRow(index);
        }
        /// <summary>
        /// 创建单元格
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public ICell CreateCell(IRow row, int index)
        {
            return row.CreateCell(index);
        }


        #endregion

        #region  //设置样式

        /// <summary>
        /// 标题行样式
        /// </summary>
        public ICellStyle _headerStyle;
        /// <summary>
        /// 单元格样式
        /// </summary>
        public ICellStyle _borderstyle;
        /// <summary>
        /// 日期单元格样式
        /// </summary>
        public ICellStyle _datecellStyleNum;
        /// <summary>
        /// 日期时间单元格样式
        /// </summary>
        public ICellStyle _datetimecellStyleNum;

        private void CellStyleInit()
        {
            //设置标题行的边框样式.
            _headerStyle = Workbook.CreateCellStyle();
            //水平与垂直居中处理.
            _headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            _headerStyle.VerticalAlignment = VerticalAlignment.Center;

            ////字体
            //IFont headerFont = Workbook.CreateFont();
            //headerFont.FontHeightInPoints = 14;
            //headerFont.Color = HSSFColor.Black.Index;
            //_headerStyle.SetFont(headerFont);

            _headerStyle.WrapText = true;  //自动换行
            //  _headerStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            // _headerStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;


            _headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            _headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            _headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            _headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            //设置每一个单元格的边框样式.

            _borderstyle = Workbook.CreateCellStyle();
            _borderstyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            _borderstyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            _borderstyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            _borderstyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

            //IFont borderSfont = Workbook.CreateFont();
            //borderSfont.FontHeightInPoints = 11;
            //borderSfont.Color = HSSFColor.Black.Index;
            //_borderstyle.SetFont(borderSfont);

            //创建日期格式化显示.
            _datecellStyleNum = Workbook.CreateCellStyle();
            IDataFormat formatNum = Workbook.CreateDataFormat();


            _datecellStyleNum.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            _datecellStyleNum.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            _datecellStyleNum.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            _datecellStyleNum.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


            //创建日期时间格式化显示.
            _datetimecellStyleNum = Workbook.CreateCellStyle();
            IDataFormat formatNum1 = Workbook.CreateDataFormat();
            _datetimecellStyleNum.DataFormat = formatNum.GetFormat("yyyy-mm-Dd hh:mm:ss");

            _datetimecellStyleNum.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            _datetimecellStyleNum.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            _datetimecellStyleNum.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            _datetimecellStyleNum.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="columnIndex">列索引</param>
        /// <param name="columnWidth">宽度</param>
        public void SetColumnWidth(int columnIndex, int columnWidth)
        {
            Sheet.SetColumnWidth(columnIndex, columnWidth);
        }


        #endregion

        #region  //EXCEL数据转换为属性值

        /// <summary>
        /// 将EXCEL值转为属性值
        /// </summary>
        /// <param name="distanceType"></param>
        /// <param name="sourceCell"></param>
        /// <returns></returns>
        private Object GetExcelCellToProperty(Type distanceType, ICell sourceCell)
        {
            object rs = distanceType.IsValueType ? Activator.CreateInstance(distanceType) : null;

            // 1.判断传递的单元格是否为空
            if (sourceCell == null || string.IsNullOrEmpty(sourceCell.ToString()))
            {
                return rs;
            }

            // 2.Excel文本和数字单元格转换，在Excel里文本和数字是不能进行转换，所以这里预先存值
            object sourceValue = null;
            switch (sourceCell.CellType)
            {
                case CellType.Blank:
                    break;

                case CellType.Boolean:
                    break;

                case CellType.Error:
                    break;

                case CellType.Formula:
                    break;

                case CellType.Numeric:
                    sourceValue = sourceCell.NumericCellValue;
                    break;

                case CellType.String:
                    sourceValue = sourceCell.StringCellValue;
                    break;

                case CellType.Unknown:
                    break;

                default:
                    sourceValue = sourceCell.StringCellValue;
                    break;
            }

            string valueDataType = distanceType.Name;

            // 在这里进行特定类型的处理
            switch (valueDataType.ToLower()) // 以防出错，全部小写
            {
                case "string":
                    rs = sourceValue.ToString();
                    break;
                case "int":
                    rs = Convert.ToInt32(sourceValue);
                    break;
                case "int16":
                    rs = Convert.ToInt16(sourceValue);
                    break;
                case "int32":
                    // rs = (int)Convert.ChangeType(sourceCell.NumericCellValue.ToString(), distanceType);
                    rs = Convert.ToInt32(sourceValue);
                    break;
                case "float":
                case "double":
                case "single":
                    rs = Convert.ToSingle(sourceCell.NumericCellValue);
                    break;
                case "datetime":
                    rs = sourceCell.DateCellValue;
                    break;
                case "timespan":
                    rs = sourceCell.DateCellValue.ToString().Split(' ').Length > 1 ? TimeSpan.Parse(sourceCell.DateCellValue.ToString().Split(' ')[1]) : new TimeSpan();
                    break;
                case "guid":
                    rs = (Guid)Convert.ChangeType(sourceCell.NumericCellValue, distanceType);
                    return rs;
            }
            return rs;
        }
        #endregion

        #region  //单一样式的EXCEL表格导入导出

        /// <summary>
        /// 将单一样式的EXCEL表数据导入到LIST中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="infos">字段，表格标题对应关系</param>
        /// <param name="titleIndex">标题行标，从0开始</param>
        /// <returns></returns>
        public List<T> ExcelToEntity<T>(List<SheetTitleInfo> infos, int titleIndex) where T : new()
        {
            List<T> resultData = new List<T>();

            int titleNumber = GetLastCellNumber(titleIndex); //获取字段列的最大单元格数

            for (int i = 0; i < titleNumber; i++)
            {
                var title = GetRowCellValue(titleIndex, i).ToString();
                var data = infos.FirstOrDefault(p => p.TitleStr == title);
                if (data != null)
                {
                    data.TitleIndex = i;
                }
            }

            int maxRow = GetLastRowNumber(0);
            for (int i = titleIndex + 1; i < maxRow; i++)
            {
                T data = new T();
                foreach (var item in infos)
                {
                    PropertyInfo perInfo = data.GetType().GetProperty(item.FieldStr);
                    perInfo?.SetValue(data, GetExcelCellToProperty(perInfo.PropertyType, Sheet.GetRow(i).GetCell(item.TitleIndex)));
                }
                resultData.Add(data);
            }
            return resultData;
        }

        /// <summary>
        /// 将Excel表数据导入到字符串数组列表中
        /// </summary>
        /// <param name="maxCountRowIndex">j最大行所在下标</param>
        /// <param name="maxCountColumnIndex">最大列所在下标</param>
        /// <returns></returns>
        public List<string[]> ExcelToArray(int maxCountRowIndex,  int maxCountColumnIndex)
        {
            List<string[]> list = new List<string[]>();
            int titleNumber = GetLastCellNumber(maxCountColumnIndex); //获取字段列的最大单元格数
            int maxRow = GetLastRowNumber(maxCountRowIndex, 1);
            for(int i=0;i< maxRow; i++)
            {
                string[] strArray=new string[titleNumber];
                for(int j = 0; j < strArray.Length; j++)
                {
                    strArray[j]= GetRowCellValue(i,j).ToString();
                }
                list.Add(strArray);
            }
            return list;

        }


        /// <summary>
        /// 数据导出到样式单一的EXCEL中
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="infos">字段名与标题对应关系</param>
        /// <param name="list">数据</param>
        /// <param name="sheetName">表格名</param>
        public void EntityToExcel(string fileName, List<SheetTitleInfo> infos, IList list, string sheetName,bool isStyle= false)
        {
            ICell cell;
            CreateWorkbook(fileName);
            CreateSheet(sheetName);
            SetSheetIndex(0);
            IRow row = Sheet.CreateRow(0);
            for (var i = 0; i < infos.Count; i++)
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(infos[i].TitleStr); // 列名为Key的值
                if (isStyle)
                {
                    cell.CellStyle = _headerStyle;
                }
              
                infos[i].TitleIndex = i;
                if (infos[i].ColumnWidth > 0)
                {
                    Sheet.SetColumnWidth(i, infos[i].ColumnWidth);
                }
                else
                {
                    Sheet.AutoSizeColumn(i);
                }

            }
            int rowIndex = 1;
            if (list.Count > 0)
            {
                foreach (var item in list)
                {
                    IRow rowTmp = Sheet.CreateRow(rowIndex);
                    foreach (var info in infos)
                    {
                        PropertyInfo proInfo = item.GetType().GetProperty(info.FieldStr);
                        if (proInfo != null)
                        {
                            var proData = proInfo.GetValue(item, null);
                            cell = rowTmp.CreateCell(info.TitleIndex);
                          
                            if (isStyle)
                            {
                                cell.CellStyle = _borderstyle;
                            }
                            if (proData != null)
                            {
                                Type dataType = proData.GetType(); ;
                                switch (dataType.Name.ToString().ToLower()) // 以防出错，全部小写
                                {
                                    case "int":
                                    case "int16":
                                    case "int32":
                                    case "float":
                                    case "double":
                                    case "single":
                                        cell.SetCellValue(Convert.ToDouble(proData));
                                        break;
                                    case "datetime":
                                        // cell.SetCellValue(Convert.ToDateTime(proData));
                                        cell.SetCellValue(proData.ToString());
                                        break;
                                    default:
                                        cell.SetCellValue(proData.ToString());
                                        break;
                                }
                            }
                        }
                    }

                    rowIndex++;
                }
            }
            //return Workbook;
        }

        /// <summary>
        /// 在当前工作表中插入一列表数据
        /// </summary>
        /// <param name="infos">字段名与标题对应关系</param>
        /// <param name="list">数据</param>
        /// <param name="titleIndex">标题行，从0开始</param>
        public void EntityToSheet(List<SheetTitleInfo> infos, IList list, int titleIndex, bool isStyle = false)
        {
            ICell cell;
            IRow row = Sheet.CreateRow(titleIndex);
            for (var i = 0; i < infos.Count; i++)
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(infos[i].TitleStr); // 列名为Key的值
                if (isStyle)
                {
                    cell.CellStyle = _headerStyle;
                }
             
                infos[i].TitleIndex = i;
                if (infos[i].ColumnWidth > 0)
                {
                    Sheet.SetColumnWidth(i, infos[i].ColumnWidth);
                }
                else
                {
                    Sheet.AutoSizeColumn(i);
                }
            }
            int rowIndex = titleIndex + 1;
            if (list.Count > 0)
            {
                foreach (var item in list)
                {
                    IRow rowTmp = Sheet.CreateRow(rowIndex);
                    foreach (var info in infos)
                    {
                        PropertyInfo proInfo = item.GetType().GetProperty(info.FieldStr);
                        if (proInfo != null)
                        {
                            var proData = proInfo.GetValue(item, null);
                            cell = rowTmp.CreateCell(info.TitleIndex);
                           
                            if (isStyle)
                            {
                                cell.CellStyle = _borderstyle;
                            }
                            if (proData != null)
                            {
                                Type dataType = proData.GetType(); ;
                                switch (dataType.Name.ToString().ToLower()) // 以防出错，全部小写
                                {
                                    case "int":
                                    case "int16":
                                    case "int32":
                                    case "float":
                                    case "double":
                                    case "single":
                                        cell.SetCellValue(Convert.ToDouble(proData));
                                        break;
                                    case "datetime":
                                        // cell.SetCellValue(Convert.ToDateTime(proData));
                                        cell.SetCellValue(proData.ToString());
                                        break;
                                    default:
                                        cell.SetCellValue(proData.ToString());
                                        break;
                                }
                            }
                        }
                    }
                    rowIndex++;
                }
            }

        }

        /// <summary>
        /// 将字符串数组列表保存到Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="list"></param>
        /// <param name="sheetName"></param>
        /// <param name="isStyle"></param>
        public void ArrayToExcel(string fileName, List<string[]> list, string sheetName, bool isStyle = false)
        {
            if (list == null || list.Count == 0)
            {
                return;
            }
            CreateWorkbook(fileName);
            CreateSheet(sheetName);
            SetSheetIndex(0);
            for (int i = 0; i < list.Count; i++)
            {
                var array = list[i];
                for (int x = 0; x < array.Length; x++)
                {
                    SetValue(i, x, array[x], isStyle);
                }
            }
        }

        /// <summary>
        /// 将字符串数组列表保存到Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="list"></param>
        /// <param name="sheetName"></param>
        /// <param name="isStyle"></param>
        public void ArrayToSheet ( List<string[]> list, string sheetName, bool isStyle = false)
        {
            if (list == null || list.Count == 0)
            {
                return;
            }
            for (int i = 0; i < list.Count; i++)
            {
                var array = list[i];
                for (int x = 0; x < array.Length; x++)
                {
                    SetValue(i, x, array[x], isStyle);
                }
            }
        }

       
        /// <summary>
        /// 将工作簿保存至文件流。
        /// </summary>
        /// <param name="fileName">路径及文件名</param>
        /// <returns></returns>
        public bool WorkBookToFileStream(string fileName)
        {
            FileStream file = new FileStream(fileName, FileMode.Create);
            Workbook.Write(file);
            file.Close();
            return true;
        }

        /// <summary>
        /// 将工作簿写入内存流中
        /// </summary>
        /// <returns></returns>
        public byte[] ExportWorkBookToMemoryStream()
        {
            using (MemoryStream ms = new MemoryStream())
            {
                Workbook.Write(ms);
                var buffer = ms.GetBuffer();
                ms.Close();
                return buffer;
            }
        }

        #endregion

        public void Dispose()
        {
            //通知垃圾回收机制不再调用终结器（析构器）
            //  GC.SuppressFinalize(this);
            Workbook.Dispose();
        }

        /// <summary>
        /// 以备程序员忘记了显式调用Dispose方法
        /// </summary>
        ~ExcelHelperEx()
        {
            Dispose();
        }
    }


    public class SheetTitleInfo
    {
        /// <summary>
        /// Sheet表标题
        /// </summary>
        public string TitleStr { get; set; }
        /// <summary>
        /// 数据字段名
        /// </summary>
        public string FieldStr { get; set; }
        /// <summary>
        /// 标题在第几列
        /// </summary>
        public int TitleIndex { get; set; }
        /// <summary>
        /// 列宽
        /// </summary>
        public int ColumnWidth { get; set; } = -1;
    }
}
